<?php defined('SYSPATH') or die('No direct script access.');

class OrgSeminar_Controller extends OrgController {

    public $REGISTRATION_LIST_UUID = 'org/orgSeminar/orgRegistrationList';
    public $CANCELLED_REGISTRATION_LIST_UUID = 'org/orgSeminar/orgCancelledRegistrationList';
    public $UNCONFIRMED_REGISTRATION_LIST_UUID = 'org/orgSeminar/orgUnconfirmedRegistrationList';
    
    public function __construct() {
        parent::__construct();
        $this->activeMenuItem = OrgMenu::$SEMINARS;
    }

    /**
     * Default action method.
     */
    public function all() {
        // Select all seminars from future, plus payment info of this organization
        $this->viewData['seminarList'] = $this->dao->queryAll(
                'SELECT semi.*, l10n.name, l10n.description ' .
                'FROM seminar semi ' .
                'LEFT OUTER JOIN seminar_l10n l10n ON semi.id = l10n.seminar_id AND l10n.language_code = :language_code ' .
                'WHERE semi.begin_date > :today',
                array(
                    'language_code' => $this->getL10nLanguageCode(),
                    'today' => date::today()
                ));
        foreach ($this->viewData['seminarList'] as &$seminar) {
            $seminar['registrationCount'] = $this->dao->countValidRegistrationsBySeminarAndOrg($seminar['id'], $this->getAuthOrg('id'));
            $seminar['paymentAcceptance'] = $this->dao->queryOne(
                    'SELECT * FROM payment_acceptance WHERE seminar_id = :seminar_id AND organization_id = :organization_id',
                    array('seminar_id' => $seminar['id'], 'organization_id' => $this->getAuthOrg('id')));
        }
        $this->renderSubview('/org/orgSeminar/orgSeminarList');
    }

    public function display() {
        $seminarId = $this->input->get('id');
        if (! valid::isId($seminarId)) {
            $this->msg->error('error.invalidId');
            $this->redirect('/org/orgSeminar/all');
        }
        
        $seminar = $this->dao->getLocalizedEntityById('seminar', $seminarId, $this->getL10nLanguageCode());
        if (! $seminar) {
            $this->msg->error('error.invalidId');
            $this->redirect('/org/orgSeminar/all');
        }
        $this->viewData['seminar'] = $seminar;
        
        // Payment acceptance
        $this->viewData['paymentAcceptance'] = $this->dao->getUnique('payment_acceptance',
            array('seminar_id' => $seminarId, 'organization_id' => $this->getAuthOrg('id')));
        $this->viewData['paymentAcceptance']['bank_account'] = $this->dao->getById('bank_account', $this->viewData['paymentAcceptance']['bank_account_id']);
        
        $this->renderSubview('org/orgSeminar/orgSeminarDisplay');
    }
    
    public function registrationList() {
        $seminarId = $this->input->get('id');
        if (! valid::isId($seminarId)) {
            $this->msg->error('error.invalidId');
            $this->redirect('/org/orgSeminar/all');
        }
        
        $seminar = $this->dao->getLocalizedEntityById('seminar', $seminarId, $this->getL10nLanguageCode());
        if (! $seminar) {
            $this->msg->error('error.invalidId');
            $this->redirect('/org/orgSeminar/all');
        }
        $this->viewData = $this->getSeminarService()->getOrgAccountingData($seminarId, $this->getAuthOrg('id'));
        $this->viewData['seminar'] = $seminar;
        $this->renderSubview('org/orgSeminar/orgRegistrationList');
    }
    
    public function registrationExcelExport() {
    	$seminarId = $this->input->get('id');
    	if (! valid::isId($seminarId)) {
    		$this->msg->error('error.invalidId');
    		$this->redirect('/org/orgSeminar/all');
    	}
    	
    	$seminar = $this->dao->getLocalizedEntityById('seminar', $seminarId, $this->getL10nLanguageCode());
    	if (! $seminar) {
    		$this->msg->error('error.invalidId');
    		$this->redirect('/org/orgSeminar/all');
    	}
    	
    	$data = $this->getSeminarService()->getOrgAccountingData($seminarId, $this->getAuthOrg('id'));
    	
    	excel::loadClasses();
    	$columnLetters = excel::columnLetters();
    	
    	$objPHPExcel = new PHPExcel();
    	excel::setMetadata($objPHPExcel);
    	$objPHPExcel->setActiveSheetIndex(0);
    	 
    	/*--------------------------------------------------
    	 * First sheet - valid registrations
    	 *--------------------------------------------------*/
    	
    	$sheet = $objPHPExcel->getActiveSheet();
    	$sheet->setTitle(text::get('orgSeminar-validRegistrationList'));
    	$rowNumber = 1;
    	
    	// Seminar title
    	$titleStyle = array(
    			'font' => array(
    					'bold' => FALSE,
    					'size' => 18,
    			),
    			'alignment' => array(
    					'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT,
    					'wrap' => FALSE,
    			),
    	);
    	$sheet->setCellValue('A' . $rowNumber, $seminar['name'] . ', ' . format::date($seminar['begin_date']) . ' - ' . format::date($seminar['end_date']));
    	$sheet->mergeCells('A' . $rowNumber . ':' . 'J' . $rowNumber);
    	$sheet->getStyle('A' . $rowNumber)->applyFromArray($titleStyle);
    	$rowNumber ++;
    	
    	// Organization name and the time of this export
    	$sheet->setCellValue('A' . $rowNumber, text::get('orgSeminar-excel-orgNameAndTimestamp', $this->getAuthOrg('name'), format::timestamp(time())));
    	$sheet->mergeCells('A' . $rowNumber . ':' . 'J' . $rowNumber);
    	$sheet->getStyle('A' . $rowNumber . ':' . 'J' . $rowNumber)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
    	$rowNumber ++;
    	
    	// Money
    	$rowNumber ++;
    	$accountsData = array(
		    	text::get('orgSeminar-billing-total') => registrationHelper::formatPrices($data['totalPriceByCurrency']),
		    	text::get('orgSeminar-billing-cancellationFees') => registrationHelper::formatPrices($data['cancellationFeesByCurrency']),
		    	text::get('orgSeminar.billing-cashTotal') => registrationHelper::formatPrices($data['totalCashPriceByCurrency']),
		    	text::get('orgSeminar.billing-bankTotal') => registrationHelper::formatPrices($data['totalBankPriceByCurrency'])
    	);
    	$columnNumber = 3;
    	foreach ($accountsData as $label => $value) {
    		$sheet->mergeCells($columnLetters[0] . $rowNumber . ':' . $columnLetters[$columnNumber] . $rowNumber);
    		$sheet->setCellValue($columnLetters[0] . $rowNumber, $label . ':');
    		$sheet->setCellValue($columnLetters[$columnNumber + 1] . $rowNumber, $value);
    		$rowNumber++;
    	}
    	$sheet->getStyle($columnLetters[0] . ($rowNumber - count($accountsData) - 1) . ':' . $columnLetters[$columnNumber + 1] . ($rowNumber - 1))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
    	$rowNumber ++;
    	
    	$exportTable = array();
    	$exportTable[] = array(
    			text::get('registration-id'),
    			text::get('registration-person_id'),
    			text::get('registration-payment_type'),
    			text::get('registration-total_price'),
    			text::get('registration-price_paid'),
    			text::get('registration-created_at'),
    			text::get('registration-confirmed_at'),
    			text::get('registration-confirmed_by_id'),
    			text::get('registration-org_notes')
    	);
    	
    	foreach ($data['registrationList'] as $registration) {
    		$row = array();
    		$row[] = $registration['id'];
    		$row[] = $registration['last_name'] . ' ' . $registration['first_name'];
    		$row[] = text::get('registration-paymentType-short-' . $registration['payment_type']);
    		$row[] = $registration['total_price'];
    		$row[] = $registration['price_paid'];
    		$row[] = format::timestamp($registration['created_at']);
    		$row[] = format::timestamp($registration['confirmed_at']);
    		$row[] = $registration['confirmed_by_first_name'] . ' ' . $registration['confirmed_by_last_name'];
    		$row[] = $registration['org_notes'];
    		$exportTable[] = $row;
    	}
    	
    	excel::writeSheetTable($sheet, $exportTable, TRUE, array(), array($rowNumber, 0));
    	
    	/*--------------------------------------------------
    	 * Second sheet - unconfirmed registrations
    	 *--------------------------------------------------*/
    	
    	$exportTable = array();
    	$exportTable[] = array(
    			text::get('registration-id'),
    			text::get('registration-person_id'),
    			text::get('registration-payment_type'),
    			text::get('registration-total_price'),
    			text::get('registration-price_paid'),
    			text::get('registration-created_at'),
    			text::get('registration-confirmed_at'),
    			text::get('registration-confirmed_by_id'),
    			text::get('registration-org_notes')
    	);
    	foreach ($data['unconfirmedRegistrationList'] as $registration) {
    		$row = array();
    		$row[] = $registration['id'];
    		$row[] = $registration['last_name'] . ' ' . $registration['first_name'];
    		$row[] = text::get('registration-paymentType-short-' . $registration['payment_type']);
    		$row[] = $registration['total_price'];
    		$row[] = $registration['price_paid'];
    		$row[] = format::timestamp($registration['created_at']);
    		$row[] = format::timestamp($registration['confirmed_at']);
    		$row[] = $registration['confirmed_by_first_name'] . ' ' . $registration['confirmed_by_last_name'];
    		$row[] = $registration['org_notes'];
    		$exportTable[] = $row;
    	}
    	
    	$sheet = $objPHPExcel->createSheet();
    	$sheet->setTitle(text::get('orgSeminar-unconfirmedRegistrationList'));
    	excel::writeSheetTable($sheet, $exportTable, TRUE);
    	
    	/*--------------------------------------------------
    	 * Thirds sheet - cancelled registrations
    	 *--------------------------------------------------*/
    	
    	$exportTable = array();
    	$exportTable[] = array(
    			text::get('registration-id'),
    			text::get('registration-person_id'),
    			text::get('registration-payment_type'),
    			text::get('registration-total_price'),
    			text::get('registration-price_paid'),
    			text::get('registration-created_at'),
    			text::get('registration-cancelled_at'),
    			text::get('registration-cancelled_by_id'),
    			text::get('registration-cancellation_fee'),
    			text::get('registration-cancellation_reason'),
    			text::get('registration-org_notes')
    	);
    	
    	foreach ($data['cancelledRegistrationList'] as $registration) {
    		$row = array();
    		$row[] = $registration['id'];
    		$row[] = $registration['last_name'] . ' ' . $registration['first_name'];
    		$row[] = text::get('registration-paymentType-short-' . $registration['payment_type']);
    		$row[] = $registration['total_price'];
    		$row[] = $registration['price_paid'];
    		$row[] = format::timestamp($registration['created_at']);
    		$row[] = format::timestamp($registration['cancelled_at']);
    		$row[] = $registration['cancelled_by_first_name'] . ' ' . $registration['cancelled_by_last_name'];
    		$row[] = $registration['cancellation_fee'];
    		$row[] = $registration['cancellation_reason'];
    		$row[] = $registration['org_notes'];
    		$exportTable[] = $row;
    	}
    	$sheet = $objPHPExcel->createSheet();
    	$sheet->setTitle(text::get('orgSeminar-cancelledRegistrationList'));
    	excel::writeSheetTable($sheet, $exportTable, TRUE);
    	
    	// Save the file
    	$objPHPExcel->setActiveSheetIndex(0);
    	$tmpExcelFileName = tempNam(Kohana::config('yosemin.temp_dir'), 'yoseminXlsx');
    	$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
    	$objWriter->save($tmpExcelFileName);
    	
    	
    	
    	download::force($tmpExcelFileName, NULL, 'Registrations.xlsx');
    }
}
